'''用Excel记录模拟交易情况,并用本脚本模拟过程,计算显示结果.
# 关于excel文件的格式:模块xlrd只支持97-2003版本的excel文件,即xls文件.
在文件中建立3个工作表(sheet),Config记录账户初始,Trades记录交易情况,Bonuses
记录分红等先进变动情况.送股请在Trades记录,把买入价设定为0就行了.交易数量的
正负也表示买卖方向,正数为买,负数为卖.
标的代码以'###'开头时,表示该项为注释,将被跳过.
每个工作表的NOTES列开始就不是脚本读取的范围,可随意笔记.

本脚本的使用方法是:用excel文件的完整路径新建ExcelAccount类,然后指定
开始与结束时间运行账户,不指定时间则是运行所有交易.
只需要改变开头的几个参数设定.
'''

import sys
from datetime import datetime

import xlrd

#####################
## 在这里改变参数  ##
#####################
EXCEL_FILE = sys.path[0]+r'\TestTrader0001.xls' # 读取的excel文件
START_TIME = '1990-01-01 09:30:01' # 开始时间
END_TIME = '2200-01-01 14:59:59' # 结束时间

class ExcelAccount:

  ## 账户信息,名字,资金,佣金率,印花税率,历史总费用
  account = {'name':'', 'money':0.0, 'feeRate':3.0/10000, 'taxRate':1.0/1000,
    'totalFee':0.0}
  ## 交易记录表,每一项为{'code':'标的代码(唯一)','name':'标的名字',
  ## 'dateTime':'时间','price':'成交价','number':'成交量'}
  ## 买入时成交量为正,卖出时为负.
  trades = []
  ## 分红或其他现金变动项表,每一项为{'code':'标的代码(唯一)','name':'标的名字',
  ## 'dateTime':'时间','cash':'资金变动数量'}
  bonuses = []
  ## 持仓字典,每一项为:
  ## key:'code'(标的代码,唯一)
  ## value:{'name':'标的名字','number':'持有量'(负数表示做空),
  ## 'price':'平均持仓成本'}
  stocks = {}
  
  ## 指定xls文件初始化
  def __init__(self, xlsPath):
    ## 打开xls文件并读取资料
    xls = xlrd.open_workbook(xlsPath)
    configSheet = xls.sheet_by_name('Config')
    tradesSheet = xls.sheet_by_name('Trades')
    bonusesSheet = xls.sheet_by_name('Bonuses')
    ## 读取config
    self.account['name'] = configSheet.cell(1,0).value
    self.account['money'] = configSheet.cell(1,1).value
    self.account['feeRate'] = configSheet.cell(1,2).value/100.0
    self.account['taxRate'] = configSheet.cell(1,3).value/100.0
    ## 读取trades
    for i in range(1, tradesSheet.nrows):
      code = tradesSheet.cell(i, 0).value
      if code.startswith('###'):
        continue
      newTrade = {
        'code': code,
        'name': tradesSheet.cell(i, 1).value,
        'dateTime': datetime.strptime(tradesSheet.cell(i, 2).value,
          "%Y-%m-%d %H:%M:%S"
        ),
        'price': tradesSheet.cell(i, 3).value,
        'number': tradesSheet.cell(i, 4).value
      }
      self.trades.append(newTrade)
    self.trades.sort(key = lambda x:x['dateTime'])
    ## 读取bonuses
    for i in range(1, bonusesSheet.nrows):
      code = bonusesSheet.cell(i, 0).value
      if code.startswith('###'):
        continue
      newBonus = {
        'code': code,
        'name': bonusesSheet.cell(i, 1).value,
        'dateTime': datetime.strptime(bonusesSheet.cell(i, 2).value,
          "%Y-%m-%d %H:%M:%S"
        ),
        'cash': bonusesSheet.cell(i, 3).value
      }     
      self.bonuses.append(newBonus)
      self.bonuses.sort(key = lambda x:x['dateTime'])
    ## 读取完成
    return
    
  ## 查看账户信息,包括持仓
  def showAccount(self):
    marketValue = self.account['money'] # 市值估计,按平均成本计算
    print('\n--- 账户信息 ---')
    for x in [('name', '帐户名'), ('money', '现金资产'), 
      ('feeRate', '佣金率'), ('taxRate', '印花税率'),
      ('totalFee', '总费用')]:
      print(('{0:>4}: {1}').format(x[1], self.account[x[0]]))
    print('\n--- 持仓情况 ---')
    for code in self.stocks:
      number = int(self.stocks[code]['number'])
      value = round(self.stocks[code]['price']*number, 2)
      marketValue += value
      if (number == 0):
        continue
      msg = [self.stocks[code]['name'],
        str(number)+'股', 
        str(round(self.stocks[code]['price'], 2))+'元',
        str(value)+'元']
      print('{0:<7} {1[0]:<5} {1[1]:<7} {1[2]:<7} {1[3]}'.format(code, msg))
    print('\n--- 市值估计: ---')
    print(str(round(marketValue,2))+'元')
    return
    
  ## 查看交易记录
  def showTrades(self):
    print('\n--- 交易记录 ---')
    for trade in self.trades:
      print((trade['dateTime']), trade['code'], trade['name'], 
        str(trade['price'])+'元', str(trade['number'])+'股'
      )
    return
    
  ## 查看现金变动部分
  def showBonuses(self):
    print('\n--- 分红等现金变动 ---')
    for bonus in self.bonuses:
      print(bonus['dateTime'], bonus['code'], bonus['name'],
        str(bonus['cash'])+'元'
      )
    return
    
  ## 执行一项交易
  def doTrade(self, trade):
    code = trade['code']
    ## 增加标的记录与查看名称是否改变
    if not (code in self.stocks):     
      self.stocks[code] = stock = {}
      stock['name'] = trade['name']
      stock['number'] = 0
      stock['price'] = 0.0
    else:
      stock = self.stocks[code]
    if (stock['name'] != trade['name']):
      stock['name'] = trade['name']
    ## 计算各项变动
    # 持仓数量与持仓成本变动
    if (stock['number']+trade['number'] == 0):
      stock['price'] = 0.0
      stock['number'] = 0     
    else:
      stock['price'] = (stock['price']*stock['number']+
        trade['price']*trade['number'])/(stock['number']+trade['number'])
      stock['number'] = stock['number']+trade['number']
    # 费用计算
    fee = max(abs(trade['number']*trade['price']*self.account['feeRate']), 5.0)
    if (trade['number'] == 0):
      fee = 0.0
    if (trade['number'] < 0) and (code[0] in ['0', '3', '6']):
      fee += abs((trade['number']*trade['price']*self.account['taxRate']))
    fee = round(fee, 2)
    # 资金变动
    self.account['money'] -= trade['number']*trade['price']+fee
    self.account['money'] = round(self.account['money'], 2)
    self.account['totalFee'] += fee
    self.account['totalFee'] = round(self.account['totalFee'], 2)
    return
    
  ## 执行一项分红或其他现金变动
  def doBonus(self, bonus):
    self.account['money'] += bonus['cash']
    return
    
  ## 主函数,执行一段时间内的所有交易与现金变动
  def main(self, startTime = '1970-1-1 00:00:01', 
    endTime = '2219-06-28 23:59:59'):
    ## 开始,结束时间,默认全部
    startTime = datetime.strptime(startTime,"%Y-%m-%d %H:%M:%S")
    endTime = datetime.strptime(endTime,"%Y-%m-%d %H:%M:%S")
    ## 按时间过滤列表,进行历史组合
    self.trades = list(filter(lambda x:(x['dateTime'] > startTime) and
      x['dateTime'] < endTime, self.trades))
    self.bonuses = list(filter(lambda x:(x['dateTime'] > startTime) and
      x['dateTime'] < endTime, self.bonuses))
    history = self.trades+self.bonuses
    history.sort(key = lambda x:x['dateTime'])
    for x in history:
      # 看x是分红还是交易,分别处理
      if 'cash' in x:
        self.doBonus(x)
      else:
        self.doTrade(x)
    ## 完成,显示账号情况
    #self.showTrades()
    #self.showBonuses()
    self.showAccount()
    return

    
## 脚本开始
if __name__ == '__main__':
  ea = ExcelAccount(xlsPath = EXCEL_FILE)
  ea.main(START_TIME, END_TIME)